using System;
using System.Collections.Generic;
using System.Data;
using MySql.Data.MySqlClient;
using WegasManagementSystem.Models;

namespace WegasManagementSystem.DAL
{
    /// <summary>
    /// 考勤数据访问类
    /// </summary>
    public class AttendanceDAL
    {
        /// <summary>
        /// 添加考勤记录
        /// </summary>
        /// <param name="attendance">考勤对象</param>
        /// <returns>新考勤ID，失败返回0</returns>
        public int AddAttendance(AttendanceInfo attendance)
        {
            string sql = @"INSERT INTO hr_attendance 
                          (emp_id, dept_id, year, month, work_days, actual_work_days, absence_days, 
                          overtime_hours, late_count, early_leave_count, created_time, updated_time) 
                          VALUES 
                          (@emp_id, @dept_id, @year, @month, @work_days, @actual_work_days, @absence_days, 
                          @overtime_hours, @late_count, @early_leave_count, @created_time, @updated_time);
                          SELECT LAST_INSERT_ID();";

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@emp_id", attendance.EmpId),
                new MySqlParameter("@dept_id", attendance.DeptId),
                new MySqlParameter("@year", attendance.Year),
                new MySqlParameter("@month", attendance.Month),
                new MySqlParameter("@work_days", attendance.WorkDays),
                new MySqlParameter("@actual_work_days", attendance.ActualWorkDays),
                new MySqlParameter("@absence_days", attendance.AbsenceDays),
                new MySqlParameter("@overtime_hours", attendance.OvertimeHours),
                new MySqlParameter("@late_count", attendance.LateCount),
                new MySqlParameter("@early_leave_count", attendance.EarlyLeaveCount),
                new MySqlParameter("@created_time", attendance.CreatedTime),
                new MySqlParameter("@updated_time", attendance.UpdatedTime)
            };

            object result = DBConnection.ExecuteScalar(sql, CommandType.Text, parameters);
            return result != null ? Convert.ToInt32(result) : 0;
        }

        /// <summary>
        /// 更新考勤记录
        /// </summary>
        /// <param name="attendance">考勤对象</param>
        /// <returns>更新成功返回true，失败返回false</returns>
        public bool UpdateAttendance(AttendanceInfo attendance)
        {
            string sql = @"UPDATE hr_attendance 
                          SET emp_id = @emp_id, dept_id = @dept_id, year = @year, month = @month, 
                          work_days = @work_days, actual_work_days = @actual_work_days, absence_days = @absence_days, 
                          overtime_hours = @overtime_hours, late_count = @late_count, early_leave_count = @early_leave_count, 
                          updated_time = @updated_time 
                          WHERE attendance_id = @attendance_id";

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@attendance_id", attendance.AttendanceId),
                new MySqlParameter("@emp_id", attendance.EmpId),
                new MySqlParameter("@dept_id", attendance.DeptId),
                new MySqlParameter("@year", attendance.Year),
                new MySqlParameter("@month", attendance.Month),
                new MySqlParameter("@work_days", attendance.WorkDays),
                new MySqlParameter("@actual_work_days", attendance.ActualWorkDays),
                new MySqlParameter("@absence_days", attendance.AbsenceDays),
                new MySqlParameter("@overtime_hours", attendance.OvertimeHours),
                new MySqlParameter("@late_count", attendance.LateCount),
                new MySqlParameter("@early_leave_count", attendance.EarlyLeaveCount),
                new MySqlParameter("@updated_time", DateTime.Now)
            };

            int affectedRows = DBConnection.ExecuteNonQuery(sql, CommandType.Text, parameters);
            return affectedRows > 0;
        }

        /// <summary>
        /// 删除考勤记录
        /// </summary>
        /// <param name="attendanceId">考勤ID</param>
        /// <returns>删除成功返回true，失败返回false</returns>
        public bool DeleteAttendance(int attendanceId)
        {
            string sql = "DELETE FROM hr_attendance WHERE attendance_id = @attendance_id";
            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@attendance_id", attendanceId)
            };

            int affectedRows = DBConnection.ExecuteNonQuery(sql, CommandType.Text, parameters);
            return affectedRows > 0;
        }

        /// <summary>
        /// 获取员工某月的考勤记录
        /// </summary>
        /// <param name="empId">员工ID</param>
        /// <param name="year">年份</param>
        /// <param name="month">月份</param>
        /// <returns>考勤对象</returns>
        public AttendanceInfo GetAttendanceByEmployee(int empId, int year, int month)
        {
            string sql = @"SELECT a.*, e.name as emp_name, d.dept_name 
                          FROM hr_attendance a 
                          JOIN emp_employee e ON a.emp_id = e.emp_id 
                          LEFT JOIN org_department d ON a.dept_id = d.dept_id 
                          WHERE a.emp_id = @emp_id AND a.year = @year AND a.month = @month";

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@emp_id", empId),
                new MySqlParameter("@year", year),
                new MySqlParameter("@month", month)
            };

            DataTable dt = DBConnection.ExecuteDataTable(sql, CommandType.Text, parameters);
            if (dt != null && dt.Rows.Count > 0)
            {
                return MapRowToAttendance(dt.Rows[0]);
            }
            return null;
        }

        /// <summary>
        /// 获取部门某月的考勤记录
        /// </summary>
        /// <param name="deptId">部门ID</param>
        /// <param name="year">年份</param>
        /// <param name="month">月份</param>
        /// <returns>考勤对象列表</returns>
        public List<AttendanceInfo> GetAttendanceByDepartment(int deptId, int year, int month)
        {
            string sql = @"SELECT a.*, e.name as emp_name, d.dept_name 
                          FROM hr_attendance a 
                          JOIN emp_employee e ON a.emp_id = e.emp_id 
                          LEFT JOIN org_department d ON a.dept_id = d.dept_id 
                          WHERE a.dept_id = @dept_id AND a.year = @year AND a.month = @month";

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@dept_id", deptId),
                new MySqlParameter("@year", year),
                new MySqlParameter("@month", month)
            };

            DataTable dt = DBConnection.ExecuteDataTable(sql, CommandType.Text, parameters);
            List<AttendanceInfo> attendanceList = new List<AttendanceInfo>();

            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    attendanceList.Add(MapRowToAttendance(row));
                }
            }

            return attendanceList;
        }

        /// <summary>
        /// 批量添加考勤记录
        /// </summary>
        /// <param name="attendanceList">考勤对象列表</param>
        /// <returns>成功添加的记录数</returns>
        public int BatchAddAttendance(List<AttendanceInfo> attendanceList)
        {
            if (attendanceList == null || attendanceList.Count == 0)
                return 0;

            int successCount = 0;
            foreach (var attendance in attendanceList)
            {
                try
                {
                    int id = AddAttendance(attendance);
                    if (id > 0)
                        successCount++;
                }
                catch
                {
                    // 忽略错误，继续处理下一个
                    continue;
                }
            }

            return successCount;
        }

        /// <summary>
        /// 从DataRow映射到AttendanceInfo对象
        /// </summary>
        /// <param name="row">数据行</param>
        /// <returns>AttendanceInfo对象</returns>
        private AttendanceInfo MapRowToAttendance(DataRow row)
        {
            AttendanceInfo attendance = new AttendanceInfo();
            attendance.AttendanceId = Convert.ToInt32(row["attendance_id"]);
            attendance.EmpId = Convert.ToInt32(row["emp_id"]);
            attendance.DeptId = Convert.ToInt32(row["dept_id"]);
            attendance.Year = Convert.ToInt32(row["year"]);
            attendance.Month = Convert.ToInt32(row["month"]);
            attendance.WorkDays = Convert.ToDecimal(row["work_days"]);
            attendance.ActualWorkDays = Convert.ToDecimal(row["actual_work_days"]);
            attendance.AbsenceDays = Convert.ToDecimal(row["absence_days"]);
            attendance.OvertimeHours = Convert.ToDecimal(row["overtime_hours"]);
            attendance.LateCount = Convert.ToInt32(row["late_count"]);
            attendance.EarlyLeaveCount = Convert.ToInt32(row["early_leave_count"]);
            attendance.CreatedTime = Convert.ToDateTime(row["created_time"]);
            attendance.UpdatedTime = Convert.ToDateTime(row["updated_time"]);

            // 可能是连接查询的额外字段
            if (row.Table.Columns.Contains("emp_name") && row["emp_name"] != DBNull.Value)
                attendance.EmpName = row["emp_name"].ToString();

            if (row.Table.Columns.Contains("dept_name") && row["dept_name"] != DBNull.Value)
                attendance.DeptName = row["dept_name"].ToString();

            return attendance;
        }

        /// <summary>
        /// 获取所有考勤记录
        /// </summary>
        /// <returns>考勤记录列表</returns>
        public List<AttendanceInfo> GetAllAttendances()
        {
            string sql = @"SELECT a.*, e.name as emp_name, d.dept_name 
                          FROM hr_attendance a 
                          JOIN emp_employee e ON a.emp_id = e.emp_id 
                          LEFT JOIN org_department d ON a.dept_id = d.dept_id 
                          ORDER BY a.year DESC, a.month DESC, e.name";

            DataTable dt = DBConnection.ExecuteDataTable(sql, CommandType.Text, null);
            List<AttendanceInfo> attendanceList = new List<AttendanceInfo>();

            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    attendanceList.Add(MapRowToAttendance(row));
                }
            }

            return attendanceList;
        }

        /// <summary>
        /// 根据ID获取考勤记录
        /// </summary>
        /// <param name="attendanceId">考勤ID</param>
        /// <returns>考勤信息对象</returns>
        public AttendanceInfo GetAttendanceById(int attendanceId)
        {
            string sql = @"SELECT a.*, e.name as emp_name, d.dept_name 
                          FROM hr_attendance a 
                          JOIN emp_employee e ON a.emp_id = e.emp_id 
                          LEFT JOIN org_department d ON a.dept_id = d.dept_id 
                          WHERE a.attendance_id = @attendance_id";

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@attendance_id", attendanceId)
            };

            DataTable dt = DBConnection.ExecuteDataTable(sql, CommandType.Text, parameters);
            if (dt != null && dt.Rows.Count > 0)
            {
                return MapRowToAttendance(dt.Rows[0]);
            }
            
            return null;
        }
    }
} 